﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Entity;

namespace DataAccess
{
    public class OrderDA
    {
        SqlDataAdapter da;

        public DataSet getOrder()
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    DataSet dt = null;
                    da = new SqlDataAdapter("selectOrder", connection);
                    da.SelectCommand.CommandType = CommandType.StoredProcedure;
                    dt = new DataSet();
                    da.Fill(dt);
                    connection.Close();
                    return dt;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }

        public bool CreateOrder(Orders o)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("addOrder", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@CustomerID", SqlDbType.Int);
                    command.Parameters["@CustomerID"].Value = o.CustomerID.ToString();

                    command.Parameters.Add("@SubCategoryID", SqlDbType.Int);
                    command.Parameters["@SubCategoryID"].Value = o.SubCategoryID.ToString();

                    command.Parameters.Add("@CreateDate", SqlDbType.DateTime);
                    command.Parameters["@CreateDate"].Value = o.CreateDate.ToString();

                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        public Orders getOrderById(int id)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand cmd = new SqlCommand("selectOrderById", connection);
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.Parameters.Add(new SqlParameter("@OrdersID", id));
                    SqlDataReader dr = cmd.ExecuteReader();
                    Orders o = null;
                    if (dr.Read())
                    {
                        int oId = Int32.Parse(dr["OrdersID"].ToString());
                        int cusId = Int32.Parse(dr["CustomerID"].ToString());
                        int subId = Int32.Parse(dr["SubCategoryID"].ToString());
                        string date = dr["CreateDate"].ToString();
                        o = new Orders(oId, cusId, subId, date);
                    }
                    connection.Close();
                    return o;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return null;
                }
            }
        }

        public bool UpdateOrder(Orders o)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("updateOrder", connection);
                    command.CommandType = CommandType.StoredProcedure;

                    command.Parameters.Add("@OrdersID", SqlDbType.Int);
                    command.Parameters["@OrdersID"].Value = o.OrdersID.ToString();

                    command.Parameters.Add("@CustomerID", SqlDbType.Int);
                    command.Parameters["@CustomerID"].Value = o.CustomerID.ToString();

                    command.Parameters.Add("@SubCategoryID", SqlDbType.Int);
                    command.Parameters["@SubCategoryID"].Value = o.SubCategoryID.ToString();

                    command.Parameters.Add("@CreateDate", SqlDbType.DateTime);
                    command.Parameters["@CreateDate"].Value = o.CreateDate.ToString();

                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

        public bool DeleteOrder(Orders o)
        {
            using (SqlConnection connection = Database.GetConnection())
            {
                try
                {
                    connection.Open();
                    SqlCommand command = new SqlCommand("removeOrder", connection);
                    command.CommandType = CommandType.StoredProcedure;
                    command.Parameters.Add(new SqlParameter("@OrdersID", o.OrdersID));
                    command.ExecuteNonQuery();
                    connection.Close();
                    return true;
                }
                catch (Exception e)
                {
                    connection.Close();
                    return false;
                }
            }
        }

    }
}
